package com.cheys.webapp.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import com.cheys.webapp.model.BBS;
import com.cheys.webapp.model.Case;
import com.cheys.webapp.model.User;
import com.cheys.webapp.util.DateUtil;
import com.cheys.webapp.util.StringUtil;

@Repository
public class CaseDao extends AbstractDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Override
	public JdbcTemplate getJdbcTemplate() {
		// TODO Auto-generated method stub
		return super.getJdbcTemplate();
	}
	@Override
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		// TODO Auto-generated method stub
		super.setJdbcTemplate(jdbcTemplate);
	}
	public Case selectOne(int id){
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("select c.id,c.title,c.content,c.resource,c.type,c.typeName,c.belong,c.createtime,c.updatetime,c.fm_resource,c.isneturl,c.parent_id from ");
		sbSql.append(Case.TABLE_NAME +" c " );
		sbSql.append(" where c.id = "+id);
		final Case cc = new Case();
		this.jdbcTemplate.query(sbSql.toString(), new RowCallbackHandler() {
			
			public void processRow(ResultSet rs) throws SQLException {
				// TODO Auto-generated method stub
				cc.setId(rs.getInt(1));
				cc.setTitle(rs.getString(2));
				cc.setContent(rs.getString(3));
				cc.setResource(rs.getString(4));
				cc.setType(rs.getInt(5));
				cc.setTypeName(rs.getString(6));
				cc.setBelong(rs.getInt(7));
				cc.setCreatetime(DateUtil.formatDate(rs.getDate(8), DateUtil.DATE_FORMAT_YYYYMMDDHHMMSS));
				if(rs.getDate(9) != null)
					cc.setUpdatetime(DateUtil.formatDate(rs.getDate(9), DateUtil.DATE_FORMAT_YYYYMMDDHHMMSS));
				cc.setFmResource(rs.getString(10));
				cc.setIsneturl(rs.getString(11));
				cc.setParentId(rs.getInt(12));
			}
		});
		return cc;
	}
	public List<Case> select(Case c) throws Exception{
		StringBuffer sbSql = new StringBuffer();
		StringBuffer sbWhere = new StringBuffer(" where c.id != 0 ");
		sbSql.append("select c.id,c.title,c.content,c.resource,c.fm_resource,c.type,c.typeName,c.belong,c.createtime,c.updatetime,c.isneturl,c.parent_id from ");
		sbSql.append(Case.TABLE_NAME + " c ");
		if(c != null){
			if(c.getId() !=0){
				sbWhere.append(" and c.id="+c.getId());
			}
			if(c.getTitle() != null && c.getTitle().length() > 0){
				sbWhere.append(" and c.title like '%"+c.getTitle()+"%'");
			}
			if(c.getContent() != null && c.getContent().length() > 0){
				sbWhere.append(" and c.content like '%"+c.getContent()+"%'");
			}
			if(c.getType() != 0){
				sbWhere.append(" and c.type = "+c.getType());
			}
			if(c.getBelong() != 0){
				sbWhere.append(" and c.belong ="+c.getBelong());
			}
			if(c.getParentId() !=0){
				sbWhere.append(" and c.parent_id ="+c.getParentId());
			}
		}
		sbWhere.append(" order by c.createtime,c.updatetime desc");
		sbSql.append(sbWhere);
		List<Case> list = this.jdbcTemplate.query(sbSql.toString(), new RowMapper<Case>() {

			public Case mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				Case cc = new Case();
				cc.setId(rs.getInt(1));
				cc.setTitle(rs.getString(2));
				cc.setContent(rs.getString(3));
				cc.setResource(rs.getString(4));
				cc.setFmResource(rs.getString(5));
				cc.setType(rs.getInt(6));
				cc.setTypeName(rs.getString(7));
				cc.setBelong(rs.getInt(8));
				cc.setCreatetime(DateUtil.formatDate(rs.getDate(9), DateUtil.DATE_FORMAT_YYYYMMDDHHMMSS));
				if(rs.getDate(10) != null)
					cc.setUpdatetime(DateUtil.formatDate(rs.getDate(10), DateUtil.DATE_FORMAT_YYYYMMDDHHMMSS));
				cc.setIsneturl(rs.getString(11));
				cc.setParentId(rs.getInt(12));
				return cc;
			}
		});
		return list;
	}
	
	public List<BBS> getBBSInfoByCaseId(int caseId){
		List<BBS> list = null;
		StringBuffer sbSql = new StringBuffer();
		sbSql.append("select b.id,b.case_id,b.content,b.pub_time,u.id,u.user_name,u.user_pwd,u.user_phone from ");
		sbSql.append(BBS.TABLE_NAME + " b,");
		sbSql.append(User.TABLE_NAME + " u ");
		sbSql.append("WHERE b.user_id = u.id and b.case_id ="+caseId+" order by b.pub_time desc");
		list = this.jdbcTemplate.query(sbSql.toString(), new RowMapper<BBS>(){

			public BBS mapRow(ResultSet rs, int arg1) throws SQLException {
				// TODO Auto-generated method stub
				BBS bbs = new BBS();
				User user = new User();
				bbs.setId(rs.getInt(1));
				bbs.setCaseId(rs.getInt(2));
				bbs.setContent(rs.getString(3));
				bbs.setPubTime(rs.getString(4));
				user.setId(rs.getInt(5));
				user.setUserName(rs.getString(6));
				user.setUserPwd(rs.getString(7));
				user.setUserPhone(rs.getString(8));
				bbs.setUser(user);
				return bbs;
			}
		});
		return list;
	}
}
